package jdbc;

import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * Description:
 *
 * @author lzy
 * @create 2018-07-31 上午8:57
 */
public class MetaDataTest {
    private static final Logger LOGGER = LoggerFactory.getLogger(MetaDataTest.class);

    /**
     * DatabaseMetaData是描述数据库的元数据
     * 可以由Connectiond得到
     * 了解即可
     */
    @Test
    public void testDataBaseMetaData() {
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();

            // 可以得到数据库本身的一些基本信息
            // 得到数据库的版本号
            int version = metaData.getDatabaseMajorVersion();
            LOGGER.info("version:" + version);

            // 得到链接数据库的用户名
            String user = metaData.getUserName();
            LOGGER.info("db user:" + user);

            // 得到MySql中有哪些数据库
            resultSet = metaData.getCatalogs();
            while (resultSet.next()) {
                LOGGER.info("db repository:" + resultSet.getString(1));
            }

        } catch (Exception e) {

        } finally {
            JdbcUtils.release(connection, null, resultSet);
        }
    }


    /**
     * ResultSetMetaData:描述结果集的元数据
     * 可以得到结果集中的基本信息：结果集中有哪些列，列名，别名等
     * 结合反射，可以写出通用的CRUD方法
     */
    @Test
    public void testResultSetMetaData() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT id uid, name uname, email em, birth birthday FROM customers";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();

            // 得到ResultSetMetaData对象
            ResultSetMetaData rsmd = resultSet.getMetaData();

            // 得到列的个数
            long columnCount = rsmd.getColumnCount();
            LOGGER.info("column numbers:" + columnCount);

            for (int i = 0; i < columnCount; i++) {
                //得到列名
                String columnName = rsmd.getColumnName(i + 1);
                LOGGER.info((i + 1) + " column name:" + columnName);
                //得到列的别名
                String columnLabel = rsmd.getColumnLabel(i + 1);
                LOGGER.info((i + 1) + " column label:" + columnLabel);
            }
        } catch (Exception e) {
            LOGGER.error("error", e);
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}
